package com.school.dao.impl;

import com.school.config.DbcpConfig;
import com.school.dao.StudentDao;
import com.school.model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl extends BaseDaoImpl<Student> implements StudentDao {

    @Override
    public Student insert(Student student) {
        String sql = "insert into " + tableName + " (stunum,name,birthday,sex,pswd) value (?,?,?,?,?)";
        try {
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = DbcpConfig.getPreparedStatement(sql);
            if (statement != null) {
                // 设置sql中的5个问好处的值
                statement.setString(1, student.getStunum());
                statement.setString(2, student.getName());
                if (student.getBirthday() != null) {
                    statement.setDate(3, new java.sql.Date(student.getBirthday().getTime()));
                }
                statement.setString(4, student.getSex());
                statement.setString(5, student.getPswd());
                // 执行上面的sql语句取查询，得到查询的条数
                statement.executeUpdate();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                while (keys.next()) {
                    student.setId(keys.getInt(1));
                }
                // 关闭查询过程
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return student;
    }

    @Override
    public List<Student> insert(List<Student> students) {
        String sql = "insert into " + tableName + " (stunum,name,birthday,sex,pswd) values (?,?,?,?,?)";
        try {
            // 获取连接对象
            Connection connection = DbcpConfig.getConnection();
            // 开启事务
            connection.setAutoCommit(false);
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (statement != null) {
                for (Student student : students) {
                    // 设置sql中的5个问好处的值
                    statement.setString(1, student.getStunum());
                    statement.setString(2, student.getName());
                    statement.setDate(3, new java.sql.Date(student.getBirthday().getTime()));
                    statement.setString(4, student.getSex());
                    statement.setString(5, student.getPswd());
                    // 正常情况需要提交，但是现在暂存一次操作
                    statement.addBatch();
                }
                // 再一次性全部提交
                statement.executeBatch();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                for (Student student : students) {
                    keys.next();
                    if (keys != null) {
                        student.setId(keys.getInt(1));
                    }
                }
                // 关闭查询过程
                statement.close();
                connection.commit();
                return students;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return new ArrayList<Student>(0);
    }


    @Override
    public int update(Student student) {
        // update student set stunum='fjsjklf', sex='女' wehere id=1
        StringBuffer sql = new StringBuffer("update " + tableName + " set ");
        if (student.getStunum() != null) {
            sql.append(" stunum = '" + student.getStunum() + "',");
        }
        if (student.getName() != null) {
            sql.append(" name = '" + student.getName() + "',");
        }
        if (student.getBirthday() != null) {
            sql.append(" birthday = '" + new Date(student.getBirthday().getTime()) + "',");
        }
        if (student.getSex() != null) {
            sql.append(" sex = '" + student.getSex() + "',");
        }
        if (student.getPswd() != null) {
            sql.append(" pswd = '" + student.getPswd() + "',");
        }
        if (student.getStatus() != null) {
            sql.append(" status = " + student.getStatus() + ",");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf(",");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 1);
        sql.append(" where id = " + student.getId());
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                return preparedStatement.executeUpdate();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int[] update(List<Student> students) {
        try {
            Connection connection = DbcpConfig.getConnection();
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            if (statement != null) {
                for (Student student : students) {
                    // update student set stunum='fjsjklf', sex='女' wehere id=1
                    StringBuffer sql = new StringBuffer("update " + tableName + " set ");
                    if (student.getStunum() != null) {
                        sql.append(" stunum = '" + student.getStunum() + "',");
                    }
                    if (student.getName() != null) {
                        sql.append(" name = '" + student.getName() + "',");
                    }
                    if (student.getBirthday() != null) {
                        sql.append(" birthday = '" + new Date(student.getBirthday().getTime()) + "',");
                    }
                    if (student.getSex() != null) {
                        sql.append(" sex = '" + student.getSex() + "',");
                    }
                    if (student.getPswd() != null) {
                        sql.append(" pswd = '" + student.getPswd() + "',");
                    }
                    if (student.getStatus() != null) {
                        sql.append(" status = " + student.getStatus() + ",");
                    }
                    // 获取”,“最后一次出现的位置下标
                    int lastIndex = sql.lastIndexOf(",");
                    // 删除最后一个”,“
                    sql.delete(lastIndex, lastIndex + 1);
                    sql.append(" where id = " + student.getId());
                    System.out.println(sql);
                    statement.addBatch(sql.toString());
                }
                // 返回成功和失败：1-成功；0-失败
                int res[] = statement.executeBatch();
                connection.commit();
                statement.close();
                return res;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new int[0];
    }


    @Override
    public Student findOneById(Integer id) {
        String sql = "select id,stunum,name,birthday,sex from " + tableName + " where id = ?";
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql);
            if (preparedStatement != null) {
                preparedStatement.setInt(1, id);
                ResultSet resultSet = preparedStatement.executeQuery();
                Student student = new Student();
                while (resultSet.next()) {
                    student.setId(resultSet.getInt("id"));
                    student.setStunum(resultSet.getString("stunum"));
                    student.setName(resultSet.getString("name"));
                    student.setBirthday(new java.util.Date(resultSet.getDate("birthday").getTime()));
                    student.setSex(resultSet.getString("sex"));
                }
                preparedStatement.close();
                return student;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new Student();
    }

    @Override
    public List<Student> findPage(Student student, Integer offset, Integer size) {
        // select * from student limit 0,10;
        StringBuffer sql = new StringBuffer("select id,stunum,name,birthday,sex from ");
        sql.append(tableName).append(" where ");
        if (student.getStunum() != null) {
            sql.append(" stunum like '%" + student.getStunum() + "%' or ");
        }
        if (student.getName() != null) {
            sql.append(" name like '%" + student.getName() + "%' or ");
        }
        if (student.getBirthday() != null) {
            sql.append(" birthday like '%" + new Date(student.getBirthday().getTime()) + "%' or ");
        }
        if (student.getSex() != null) {
            sql.append(" sex like '%" + student.getSex() + "%' or ");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf("or");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 2);
        sql.append(" limit ?,?");
        System.out.println(sql.toString());
        List<Student> list = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, size);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    Student students = new Student();
                    students.setId(resultSet.getInt("id"));
                    students.setStunum(resultSet.getString("stunum"));
                    students.setName(resultSet.getString("name"));
                    students.setBirthday(new java.util.Date(resultSet.getDate("birthday").getTime()));
                    students.setSex(resultSet.getString("sex"));
                    list.add(students);
                }
                preparedStatement.close();
                return list;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
}
